package com.ncepu_ideal.maven_testng.controller;

import com.ncepu_ideal.maven_testng.model.Student;
import com.ncepu_ideal.maven_testng.utils.DbHelp;
import com.ncepu_ideal.maven_testng.utils.RandomIdGenerator;
import com.ncepu_ideal.maven_testng.utils.Result;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;

/**
 * @description:
 * @author: ChengKai
 * @time: 2023/3/3 8:58
 */
public class StudentController {
    //增
    public static Result addBySql(Student s) {
        Connection con = null;
        PreparedStatement pstmt = null;
        Map<String, Object> resultDataMap = new HashMap<>();
        Result res;

        if (s.getStudentId() == null || "".equals(s.getStudentId())) {
            String id = RandomIdGenerator.generateId();
            s.setStudentId(id);
        }
        try {
            String sql = "Insert into student (student_id, Sname, Sage, Ssex, Dept_id, Sclass)"
                    + "values('" + s.getStudentId() + "','" + s.getSName() + "','" + s.getSAge() + "','" + s.getSSex() + "','" + s.getDeptId() + "','" + s.getSClass() + "')";

            List<Object> retVal = DbHelp.executeSql(sql);
            pstmt = (PreparedStatement) retVal.get(1);
            con = (Connection) retVal.get(2);

            resultDataMap.put("studentId", s.getStudentId());
            res = Result.ok(resultDataMap);

        } catch (SQLException e) {
            e.printStackTrace();
            resultDataMap.put("error", e);
            res = Result.fail(resultDataMap);
        } finally {
            DbHelp.release(null, pstmt, con);
        }
        return res;
    }
    public static Result addByCall(Student s) {
        Connection con = null;
        PreparedStatement pstmt = null;
        Map<String, Object> resultDataMap = new HashMap<String, Object>();
        Result res;

        try {
            String sql = "call student_add('" + s.getStudentId() + "','" + s.getSName() + "','" + s.getSAge() + "','" + s.getSSex() + "','" + s.getDeptId() + "','" + s.getSClass() + "')";

            List<Object> retVal = DbHelp.executeSql(sql);
            pstmt = (PreparedStatement) retVal.get(1);
            con = (Connection) retVal.get(2);

            resultDataMap.put("studentId", s.getStudentId());
            res = Result.ok(resultDataMap);

        } catch (SQLException e) {
            e.printStackTrace();
            resultDataMap.put("error", e);
            res = Result.fail(resultDataMap);
        } finally {
            DbHelp.release(null, pstmt, con);
        }
        return res;
    }
    //删
    public static Result deleteBySqlId(String id) {
        Connection con = null;
        PreparedStatement pstmt = null;
        Map<String, Object> resultDataMap = new HashMap<String, Object>();
        Result res;

        try {
            String sql = "delete from student where Student_id='" + id + "'";

            List<Object> retVal = DbHelp.executeSql(sql);
            pstmt = (PreparedStatement) retVal.get(1);
            con = (Connection) retVal.get(2);

            resultDataMap.put("studentId", id);
            res = Result.ok(resultDataMap);

        } catch (SQLException e) {
            e.printStackTrace();
            resultDataMap.put("error", e);
            res = Result.fail(resultDataMap);
        } finally {
            DbHelp.release(null, pstmt, con);
        }
        return res;
    }
    public static Result deleteByCallId(String id) {
        Connection con = null;
        PreparedStatement pstmt = null;
        Map<String, Object> resultDataMap = new HashMap<String, Object>();
        Result res;

        try {
            String sql = "call student_delete('" + id + "')";

            List<Object> retVal = DbHelp.executeSql(sql);
            pstmt = (PreparedStatement) retVal.get(1);
            con = (Connection) retVal.get(2);

            resultDataMap.put("studentId", id);
            res = Result.ok(resultDataMap);

        } catch (SQLException e) {
            e.printStackTrace();
            resultDataMap.put("error", e);
            res = Result.fail(resultDataMap);
        } finally {
            DbHelp.release(null, pstmt, con);
        }
        return res;
    }
    //改
    public static Result updateBySql(Student s) {
        Connection con = null;
        PreparedStatement pstmt = null;
        Map<String, Object> resultDataMap = new HashMap<String, Object>();
        Result res;

        try {

            String sql = "update student set Sname='" + s.getSName() + "',Sage='" + s.getSAge() + "',Ssex='" + s.getSSex() + "',Dept_id='" + s.getDeptId() + "',Sclass='" + s.getSClass() + "' where Student_id='" + s.getStudentId() + "'";

            List<Object> retVal = DbHelp.executeSql(sql);
            pstmt = (PreparedStatement) retVal.get(1);
            con = (Connection) retVal.get(2);

            resultDataMap.put("studentId", s.getStudentId());
            res = Result.ok(resultDataMap);

        } catch (SQLException e) {
            e.printStackTrace();
            resultDataMap.put("error", e);
            res = Result.fail(resultDataMap);
        } finally {
            DbHelp.release(null, pstmt, con);
        }
        return res;
    }
    public static Result updateByCall(Student s) {
        Connection con = null;
        PreparedStatement pstmt = null;
        Map<String, Object> resultDataMap = new HashMap<String, Object>();
        Result res;

        try {
            String sql = "call student_update('" + s.getStudentId() + "','" + s.getSName() + "','" + s.getSAge() + "','" + s.getSSex() + "','"+s.getDeptId() + "','" + s.getSClass() + "')";

            List<Object> retVal = DbHelp.executeSql(sql);
            pstmt = (PreparedStatement) retVal.get(1);
            con = (Connection) retVal.get(2);

            resultDataMap.put("studentId", s.getStudentId());
            res = Result.ok(resultDataMap);

        } catch (SQLException e) {
            e.printStackTrace();
            resultDataMap.put("error", e);
            res = Result.fail(resultDataMap);
        } finally {
            DbHelp.release(null, pstmt, con);
        }
        return res;
    }
    //查
    private static Result search(String sql) {
        Connection con = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        Student s = new Student();
        Map<String, Object> resultDataMap = new HashMap<String, Object>();
        Result res;
        ArrayList<Student> dataList = new ArrayList<>();
        try {
            List<Object> retVal = DbHelp.executeSql(sql);
            rs = (ResultSet) retVal.get(0);
            pstmt = (PreparedStatement) retVal.get(1);
            con = (Connection) retVal.get(2);

            while (rs.next()) {
                //封装数据
                s.setStudentId(rs.getString("Student_id"));
                s.setSName(rs.getString("Sname"));
                s.setSAge(rs.getInt("Sage"));
                s.setSSex(rs.getString("Ssex"));
                s.setDeptId(rs.getString("Dept_id"));
                s.setSClass(rs.getString("Sclass"));
                dataList.add(s);
            }

            resultDataMap.put("records", dataList);
            res = Result.ok(resultDataMap);

        } catch (SQLException e) {
            e.printStackTrace();
            resultDataMap.put("error", e);
            res = Result.fail(resultDataMap);
        } finally {
            DbHelp.release(null, pstmt, con);
        }
        return res;
    }

    public static Result searchBySqlId(String id) {
        String sql;

        if (id.length() != 0) {
            sql = "Select * from student where Student_id ='" + id + "'";
        } else {
            sql = "Select * from student";
        }
        return search(sql);
    }


    public static Result searchByCallId(String id) {
        String sql = "call student_search('" + id + "')";
        return search(sql);
    }
}
